package com.sql.connection;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class ConnectionTest {

	public static void main(String[] args) throws SQLException, NamingException, ClassNotFoundException {
		System.out.println("---TEST JDBC CONCEPT---");
		//JDBCConcept();
		
		System.out.println("----TEST PREPAREDSTATEMENT----");
		//testPrepareStatement();
		
		System.out.println("----TEST CALLABLESTATEMENT----");
		//testCallableStatement();
		
		System.out.println("---TEST CONNECTION POOLING---");
		System.out.println("See more detail in class ConnectionPoolByDataSource");
	}
	
	private static void JDBCConcept() throws SQLException {
		Connection conn = DBConnection.getConnection();
		/*
		 * AutoCommit: Handle transaction
		 * 	- If AutoCommit --> commit to db each statement
		 * 	- If NOT AutoCommit --> commit when call conn.commit(); Best performance when perform batch insert, update, delete
		 */
		conn.setAutoCommit(false);
		/*
		 * Transaction Isolation
		 * http://www.precisejava.com/javaperf/j2ee/JDBC.htm
		 * 		Transaction Level				Dirty Read		Non Repeatable Read		Phantom Read		Performance
		 * 		TRANSACTION_NONE	 			N/A				N/A						N/A					FASTEST
		 *		TRANSACTION_READ_UNCOMMITED		YES				YES						YES					FASTEST
		 *		TRANSACTION_READ_COMMITED (D)	NO				YES						YES					FAST
		 *		TRANSACTION_REPEATABLE_READ		NO				NO						YES					MEDIUM
		 *		TRANSACTION_SERIALIZABLE		NO				NO						NO					SLOW
		 *	- Dirty Read:
		 *		+ Step 1:    Database row has PRODUCT = A001 and PRICE = 10
		 *		+ Step 2:    Connection1 starts  Transaction1 (T1) .
		 *		+ Step 3:    Connection2 starts  Transaction2 (T2) .
		 *		+ Step 4:    T1 updates PRICE =20 for PRODUCT = A001
		 *		+ Step 5:    Database has now PRICE = 20 for PRODUCT = A001
		 *		+ Step 6:    T2 reads PRICE = 20 for PRODUCT = A001
		 *		+ Step 7:    T2 commits transaction
		 *		+ Step 8:    T1 rollbacks the transaction because of some problem
		 *		The problem is that T2 gets wrong PRICE=20 for PRODUCT = A001 instead of 10 because of uncommitted read. 
		 *		Obviously it is very dangerous in critical transactions if you read inconsistent data. 
		 *		If you  are sure about not accessing data concurrently  then you can allow this problem by setting TRANSACTION_READ_UNCOMMITED or TRANSACTION_NONE that in turn improves performance otherwise you have to use TRANSACTION_READ_COMMITED to avoid this problem.
		 *	- Repeatable Read
		 *		+ Step 1:    Database row has PRODUCT = A001 and PRICE = 10
		 *		+ Step 2:    Connection1 starts  Transaction1 (T1) .
		 *		+ Step 3:    Connection2 starts  Transaction2 (T2) .
		 *		+ Step 4:    T1 reads PRICE =10 for PRODUCT = A001
		 *		+ Step 5:    T2 updates PRICE = 20 for PRODUCT = A001
		 *		+ Step 6:    T2 commits transaction
		 *		+ Step 7:    Database row has PRODUCT = A001 and PRICE = 20
		 *		+ Step 8:    T1 reads PRICE = 20 for PRODUCT = A001
		 *		+ Step 9:    T1 commits transaction
		 *		Here the problem is that Transaction1 reads 10 first time and reads 20 second time but it is supposed to be 10 always whenever it reads a record in that transaction. 
		 *		You can control this problem by setting isolation level as TRANSACTION_REPEATABLE_READ.
		 *	- Phantom Read
		 *		+ Step 1:     Database has a row PRODUCT = A001 and COMPANY_ID = 10
		 *		+ Step 2:    Connection1 starts  Transaction1 (T1) .
		 *		+ Step 3:    Connection2 starts  Transaction2 (T2) .
		 *		+ Step 4:    T1 selects a row with a condition SELECT PRODUCT WHERE COMPANY_ID = 10
		 *		+ Step 5:    T2 inserts a row with a condition INSERT PRODUCT=A002  WHERE COMPANY_ID= 10
		 *		+ Step 6:    T2 commits transaction
		 *		+ Step 7:    Database has 2 rows with that condition
		 *		+ Step 8:    T1 select again with a condition SELECT PRODUCT WHERE COMPANY_ID=10 and gets 2 rows instead of 1 row
		 *		+ Step 9:    T1 commits transaction
		 *		Here the problem is that T1 gets 2 rows instead of 1 row up on selecting the same condition second time. 
		 *		You can control this problem by setting isolation level as TRANSACTION_SERIALIZABLE
		 */
		conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);  //Default TRANSACTION_READ_COMMITTED
		System.out.println(conn.getTransactionIsolation());
		long start = System.currentTimeMillis();
		/*
		 * ResultSet:
		 * 	- Type:
		 * 		+ ResultSet.TYPE_FORWARD_ONLY: (Default) read forward row1, row2, ... Can not read backward.
		 * 		+ ResultSet.TYPE_SCROLL_INSENSITIVE: Can read backward and can jump to absolute position. If a record of ResultSet is changed in database by another thread, it will not be reflected in current resultset.
		 * 		+ ResultSet.TYPE_SCROLL_SENSITIVE: Can read backward and can jump to absolute position. If a record of ResultSet is changed in database by another thread, it will be reflected in current resultset.
		 * 		*NOTE* Test TYPE_SCROLL_SENSITIVE must attend to fetchsize.
		 * 	- Concurrency:
		 * 		+ ResultSet.CONCUR_READ_ONLY: means that the ResultSet can only be read.
		 * 		+ ResultSet.CONCUR_UPDATABLE: means that the ResultSet can both read and update.
		 * 	- Holdability:
		 * 		+ ResultSet.CLOSE_CURSORS_OVER_COMMIT: Close ResultSet when call conn.commit();
		 * 		+ ResultSet.HOLD_CURSORS_OVER_COMMIT: Keep ResultSet open when call conn.commit(); and can use for another transaction. It's best when update, delete on ResultSet.
		 */
		
		/*
		 * PreparedStatement gives better performance when compared to Statement because it is pre-parsed and pre-compiled by the database once for the first time and then onwards it reuses the parsed and compiled statement.
		 * CallableStatement gives better performance when compared to PreparedStatement and Statement when there is a requirement for single request to process multiple complex statements. It parses and stores the stored procedures in the database and does all the work at database itself that in turn improves performance. 
		 */
		Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
		/*
		 * Statement.setFetchSize(30): Here it retrieves 30 rows at a time for all result sets of this statement.
		 * 	- Turn on cursor ResultSet by using setFetchSize(30). So, 30 rows will be cached in memory. 
		 *  - Turn off cursor ResultSet by using setFetchSize(0). It is default, so all rows is cached.
		 */
		st.setFetchSize(100);
		ResultSet rs = st.executeQuery("Select * FROM starworks.XbrlOutput");
		/*
		 * ResultSet.setFetchSize(30): It overrides the Statement behaviour.
		 */
		//rs.setFetchSize(30);
		
		/*
		 * Always use getXXX(...) for bester performance. If you give non recommended parameters, it needs to cast it to proper java data type that is  expensive
		 */
		while(rs.next()) {
			System.out.println("begin");
			for(int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
				System.out.print(rs.getString(i) + " -- ");
			}
			System.out.println();
		}
		System.out.println("TIME: " + (System.currentTimeMillis() - start));
		rs.close();
		st.close();
		conn.close();
	}
	
	private static void testPrepareStatement() throws SQLException {
		Connection conn = DBConnection.getConnection();
		
		/*
		 * Key points:
		 * 	- Easy to reuse
		 * 	- Better Performance: 
		 * 		+ Two levels of potential reuse:
		 * 			* Application Level: like code below. 
		 * 			  First of all, the JDBC driver can cache PreparedStatement objects internally, and thus reuse the PreparedStatement objects. This may save a little of the PreparedStatement creation time.
		 * 			* Database Level: Database take time to parser and create query plan for best efficient way. 
		 * 			  Second, the cached parsing and query plan could potentially be reused across Java applications, for instance application servers in a cluster, using the same database.
		 * 
		 */
		String sql = "UPDATE starworks.XbrlOutput SET BranchCode=? , Remark=? where Seq=?";
		PreparedStatement preparedStatement = conn.prepareStatement(sql);
		preparedStatement.setString(1, "001");
		preparedStatement.setString(2, "Testing...");
		preparedStatement.setLong  (3, 1143);
		int rowsAffected = preparedStatement.executeUpdate();
		System.out.println(rowsAffected);
		
		//Reuse PreparedStatement
		preparedStatement.setString(1, "001");
		preparedStatement.setString(2, "Testing 2...");
		preparedStatement.setLong  (3, 1144);
		rowsAffected = preparedStatement.executeUpdate();
		
		preparedStatement.close();
		conn.close();
	}
	
	private static void testCallableStatement() throws SQLException {
		Connection conn = DBConnection.getConnection();
		
		CallableStatement callableStatement = conn.prepareCall("{call starworks.usp_XbrlOutput_SelectByListSeqs(?)}");
		callableStatement.setString(1, "1143");
		ResultSet rs = callableStatement.executeQuery();
		while(rs.next()) {
			for(int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
				System.out.print(rs.getString(i) + " -- ");
			}
			System.out.println();
		}
		
		rs.close();
		callableStatement.close();
		conn.close();
	}
}
